﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BaseDal
{
    /// <summary>
    /// AssetInvDal 类实现了资产管理的数据访问层。
    /// 该类采用单例模式，确保只有一个实例存在。
    /// </summary>
    public class AssetInvDal
    {
        // 使用 Lazy<T> 实现单例模式，确保实例的惰性初始化和线程安全。
        private static readonly Lazy<AssetInvDal> _instance = new Lazy<AssetInvDal>(() => new AssetInvDal());

        /// <summary>
        /// 获取 AssetInvDal 类的单例实例。
        /// </summary>
        public static AssetInvDal Instance { get; } = _instance.Value;

        // 私有构造函数，防止外部实例化该类。
        private AssetInvDal()
        { }

        private const string tableH = "AssetHead";

        private const string tableB = "AssetBody";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableH);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,AssetNo,AssetName,Specs,Price,Unit,TypeID,TypeName,BuyDate,DeptID,DeptName,StaffID,StaffName,");
            sb.Append("Location,IsOK,RFIDNo,MaintainTimeStart,MaintainTimeEnd,M_Cycle,SupplierID,SupplierName,S_Contacts,S_Phone,AssetStatus ");
            sb.Append("FROM " + tableH);
            sb.Append(" Where IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<AssetHead> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<AssetHead>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AssetHead GetModelById(int id)
        {
            // 查询主表
            AssetHead model;
            StringBuilder sb = GetSelectSql(" and ID=@id");

            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = CommonOperation.DataTableToModel<AssetHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetAssetBodyList(model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AssetHead GetModelByNo(string assetNo)
        {
            // 查询主表
            AssetHead model;
            StringBuilder sb = GetSelectSql(" and AssetNo=@assetNo");

            SqlParameter[] parameters =
            {
                new SqlParameter("@assetNo",assetNo)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = CommonOperation.DataTableToModel<AssetHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetAssetBodyList(model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 查询附属资产
        /// </summary>
        /// <param name="headID"></param>
        /// <returns></returns>
        public List<AssetBody> GetAssetBodyList(decimal headID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,ItemID,ItemName,Specs,Unit,Price ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 and HeadID=@headID ");
            SqlParameter[] param1 = { new SqlParameter("@headID", headID) };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.ConvertDataTableToModelList<AssetBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 查询处于维护提醒时间内的资产
        /// </summary>
        /// <returns></returns>
        public List<AssetHead> GetKeepTimeWarnAsset()
        {
            string strWhere = " and AssetStatus in(0,1) and GETDATE() BETWEEN MaintainTimeStart and MaintainTimeEnd";
            StringBuilder sb = GetSelectSql(strWhere);

            DataSet ds = SqlHelper.Query(sb.ToString());

            return CommonOperation.ConvertDataTableToModelList<AssetHead>(ds.Tables[0]);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddAsset(AssetHead model)
        {
            // 定义主表SQL语句和参数
            StringBuilder hSql = new StringBuilder();
            hSql.Append("insert into " + tableH + " (");
            hSql.Append("AssetNo,AssetName,Specs,Price,Unit,TypeID,TypeName,BuyDate,DeptID,DeptName,StaffID,StaffName,Location,IsOK,");
            hSql.Append("RFIDNo,MaintainTimeStart,MaintainTimeEnd,M_Cycle,SupplierID,SupplierName,S_Contacts,S_Phone)");
            hSql.Append(" OUTPUT inserted.ID values (");
            hSql.Append("@assetNo,@assetName,@specs,@price,@unit,@typeID,@typeName,@buyDate,@deptID,@deptName,@staffID,@staffName,@location,@isOK,");
            hSql.Append("@RFIDNo,@maintainTimeStart,@maintainTimeEnd,@cycle,@supplierID,@supplierName,@contacts,@phone)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(hSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@assetNo", model.AssetNo);
                    command1.Parameters.AddWithValue("@assetName", model.AssetName);
                    command1.Parameters.AddWithValue("@specs", model.Specs);
                    command1.Parameters.AddWithValue("@price", model.Price);
                    command1.Parameters.AddWithValue("@unit", model.Unit);
                    command1.Parameters.AddWithValue("@typeID", model.TypeID);
                    command1.Parameters.AddWithValue("@typeName", model.TypeName);
                    command1.Parameters.AddWithValue("@buyDate", model.BuyDate);
                    command1.Parameters.AddWithValue("@deptID", model.DeptID);
                    command1.Parameters.AddWithValue("@deptName", model.DeptName);
                    command1.Parameters.AddWithValue("@staffID", model.StaffID);
                    command1.Parameters.AddWithValue("@staffName", model.StaffName);
                    command1.Parameters.AddWithValue("@location", model.Location);
                    command1.Parameters.AddWithValue("@isOK", model.IsOK);
                    command1.Parameters.AddWithValue("@RFIDNo", model.RFIDNo);
                    command1.Parameters.AddWithValue("@maintainTimeStart", model.MaintainTimeStart);
                    command1.Parameters.AddWithValue("@maintainTimeEnd", model.MaintainTimeEnd);
                    command1.Parameters.AddWithValue("@cycle", model.M_Cycle);
                    command1.Parameters.AddWithValue("@supplierID", model.SupplierID);
                    command1.Parameters.AddWithValue("@supplierName", model.SupplierName);
                    command1.Parameters.AddWithValue("@contacts", model.S_Contacts);
                    command1.Parameters.AddWithValue("@phone", model.S_Phone);
                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    long newID = 0;
                    if (result != null && result is decimal) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt64(result);
                        model.ID = newID;
                    }
                    //int newID = (int)command1.ExecuteScalar();

                    // 子表数据
                    List<AssetBody> itemList = model.ItemList;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder itemSql;
                        foreach (AssetBody itemModel in itemList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("insert into " + tableB + "(");
                            itemSql.Append("HeadID,ItemID,ItemName,Specs,Unit,Price)");
                            itemSql.Append(" values (");
                            itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price)");

                            using (command2 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@headID", newID);
                                command2.Parameters.AddWithValue("@itemID", itemModel.ItemID);
                                command2.Parameters.AddWithValue("@itemName", itemModel.ItemName);
                                command2.Parameters.AddWithValue("@specs", itemModel.Specs);
                                command2.Parameters.AddWithValue("@unit", itemModel.Unit);
                                command2.Parameters.AddWithValue("@price", itemModel.Price);

                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);

            return true;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateAsset(AssetHead model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableH + " set ");
            strSql.Append("AssetName=@assetName,");
            strSql.Append("Specs=@specs,");
            strSql.Append("Price=@price,");
            strSql.Append("Unit=@unit,");
            strSql.Append("TypeID=@typeID,");
            strSql.Append("TypeName=@typeName,");
            strSql.Append("BuyDate=@buyDate,");
            strSql.Append("DeptID=@deptID,");
            strSql.Append("DeptName=@deptName,");
            strSql.Append("StaffID=@staffID,");
            strSql.Append("StaffName=@staffName,");
            strSql.Append("Location=@location,");
            strSql.Append("IsOK=@isOK,");
            strSql.Append("RFIDNo=@RFIDNo,");
            strSql.Append("MaintainTimeStart=@maintainTimeStart,");
            strSql.Append("MaintainTimeEnd=@maintainTimeEnd,");
            strSql.Append("M_Cycle=@cycle,");
            strSql.Append("SupplierID=@supplierID,");
            strSql.Append("SupplierName=@supplierName,");
            strSql.Append("S_Contacts=@contacts,");
            strSql.Append("S_Phone=@phone ");
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@assetName", model.AssetName),
                    new SqlParameter("@specs", model.Specs),
                    new SqlParameter("@price", model.Price),
                    new SqlParameter("@unit", model.Unit),
                    new SqlParameter("@typeID", model.TypeID),
                    new SqlParameter("@typeName", model.TypeName),
                    new SqlParameter("@buyDate", model.BuyDate),
                    new SqlParameter("@deptID", model.DeptID),
                    new SqlParameter("@deptName", model.DeptName),
                    new SqlParameter("@staffID", model.StaffID),
                    new SqlParameter("@staffName", model.StaffName),
                    new SqlParameter("@location", model.Location),
                    new SqlParameter("@isOK", model.IsOK),
                    new SqlParameter("@RFIDNo", model.RFIDNo),
                    new SqlParameter("@maintainTimeStart", model.MaintainTimeStart),
                    new SqlParameter("@maintainTimeEnd", model.MaintainTimeEnd),
                    new SqlParameter("@cycle", model.M_Cycle),
                    new SqlParameter("@supplierID", model.SupplierID),
                    new SqlParameter("@supplierName", model.SupplierName),
                    new SqlParameter("@contacts", model.S_Contacts),
                    new SqlParameter("@phone", model.S_Phone),
                    new SqlParameter("@id", model.ID)
            };
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            //先删除子表数据
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + tableB + " where HeadID=@headID ");
            SqlParameter[] parameters2 = { new SqlParameter("@headID", model.ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //添加子表数据
            if (model.ItemList != null)
            {
                StringBuilder itemSql;
                foreach (AssetBody modelt in model.ItemList)
                {
                    itemSql = new StringBuilder();
                    itemSql.Append("insert into " + tableB + "(");
                    itemSql.Append("HeadID,ItemID,ItemName,Specs,Unit,Price)");
                    itemSql.Append(" values (");
                    itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price)");
                    SqlParameter[] parameters3 = {
                            new SqlParameter("@headID", model.ID),
                            new SqlParameter("@itemID", modelt.ItemID),
                            new SqlParameter("@itemName", modelt.ItemName),
                            new SqlParameter("@specs", modelt.Specs),
                            new SqlParameter("@unit", modelt.Unit),
                            new SqlParameter("@price", modelt.Price)
                    };
                    cmd = new CommandInfo(itemSql.ToString(), parameters3);
                    sqllist.Add(cmd);
                }
            }
            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>
        public bool DeleteAsset(int ID)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除子表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@headID");
            SqlParameter[] parameters = { new SqlParameter("@headID", ID) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除主表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update " + tableH + " set IsDelete=1");
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 修改资产状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateStatus(decimal id, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableH + " SET ");
            sb.Append("AssetStatus=@status ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }

        public int UpdAsset_MCycle(decimal id, DateTime start, DateTime end)
        {
            var sb = new StringBuilder();
            sb.Append("Update " + tableH + " SET ");
            sb.Append("MaintainTimeStart=@start,");
            sb.Append("MaintainTimeEnd=@end ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@start",start),
                new SqlParameter("@end",end),
                new SqlParameter("@id",id)
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }
    }
}